package com.xjj.service.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xjj.entity.Advice;
import com.xjj.service.dto.AdviceDto;
import com.xjj.service.dto.AdviceReadDto;
import com.xjj.service.dto.StudentReadDto;
import com.xjj.service.sqlprovider.AdviceProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface AdviceMapper extends BaseMapper<Advice> {
    @UpdateProvider(type = AdviceProvider.class,method = "update")
    void updateAdvice(Advice advice);

    @SelectProvider(type = AdviceProvider.class,method = "select")
    List<Advice> selectByPages(Advice advice);

    @Select("SELECT a.*,k.NAME className,m.NAME publisher FROM xjj_advice a join xjj_manager m on a.publisher_id = m.id LEFT JOIN xjj_klass k ON k.id = a.klass_id WHERE a.id = #{id}")
    // mysql字段映射，联表：管理员表，班级表，通知表
    @Results(id = "adviceMap",value={
            @Result(column = "file_url",property = "fileUrl"),
            @Result(column = "pub_range",property = "pubRange"),
            @Result(column = "advice_category_id",property = "adviceCategoryId")
    })
    AdviceDto getAdviceById(Integer id);


    @Select("select count(*) from xjj_parent_advice where advice_id = #{id}")
    Integer getNum(int id);

    @Select("select x.id classId,x.name className,count(*) readNum"+
            " from xjj_parent_advice a,xjj_parent p,xjj_student s,xjj_klass x "+
            "WHERE a.advice_id = #{id} AND p.id = a.parent_id "+
            "AND s.parent_id = p.id AND x.id = s.klass_id "+
            "group by x.name")
    // 查询出来的结果：classname,readnum
    List<AdviceReadDto> getStudentReadDto(int id);

    @Select("select x.id classId,x.name className,count(*) readNum"+
            " from xjj_parent_advice a,xjj_parent p,xjj_student s,xjj_klass x "+
            "WHERE a.advice_id = #{id} and s.klass_id = #{klassid} "+
            "AND p.id = a.parent_id "+
            "AND s.parent_id = p.id AND x.id = s.klass_id ")
        // 查询出来的结果：classname,readnum
    AdviceReadDto getStudentReadDtoOneClass(@Param("id") int id,@Param("klassid") int klassid);

    @Select("select id sid,name from xjj_student where klass_id =#{classId}")
    List<StudentReadDto> getReadStuList(Integer classId);

    @Select("SELECT s.id sid,s.name name FROM xjj_student s,xjj_parent p ,xjj_parent_advice pa"+
            " WHERE s.parent_id = p.id and pa.parent_id = p.id and pa.advice_id = #{adviceId}")
    List<StudentReadDto> getReadStu(Integer adviceId);

    @Select("select a.*,m.account publisher from xjj_advice a,xjj_manager m "+
            "where pub_range = #{range} and a.publisher_id = m.id group by time DESC")
    List<Advice> getParentAdvice(String range);

    @Select("select * from xjj_advice a where a.klass_id in" +
            "(select klass_id from xjj_student s,xjj_parent p where s.parent_id = p.id and p.id =#{pid})"+
            "order by a.time desc")
    List<Advice> getClassAdvice(int pid);
}
